package com.ppgames.gm.gmsystem.util;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.math.BigDecimal;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author <a href="mailto:Li7nux@gmail.com">Li7nux</a>
 * Date: 2017-11-03 10:04
 * Description: 调用原生的 connection 操作数据库
 */
public class DBConnectionUtil {

    private static final Logger logger = LoggerFactory.getLogger(DBConnectionUtil.class);

    public static Integer executeUpdate(Connection connection, String sql, List<Object> params) throws SQLException {

        PreparedStatement stat = connection.prepareStatement(sql);

        for (int i = 0; i < params.size(); i++) {

            Object param = params.get(i);

            if (param instanceof String) {
                stat.setString(i + 1, String.valueOf(param));
            } else if (param instanceof BigDecimal) {
                stat.setBigDecimal(i + 1, (BigDecimal) param);
            } else {
                stat.setString(i + 1, String.valueOf(param));
            }

        }

        int result = stat.executeUpdate();

        stat.close();

        return result;

    }

    public static List<HashMap<String, Object>> executeQuery(Connection connection, String sql, List<Object> params) throws SQLException {

        List<HashMap<String, Object>> resultList = new ArrayList<>();

        PreparedStatement stat = connection.prepareStatement(sql);

        for (int i = 0; i < params.size(); i++) {
            Object param = params.get(i);
            if (param instanceof String) {
                stat.setString(i + 1, String.valueOf(param));
            } else if (param instanceof BigDecimal) {
                stat.setBigDecimal(i + 1, (BigDecimal) param);
            } else {
                stat.setString(i + 1, String.valueOf(param));
            }
        }

        ResultSet resultSet = stat.executeQuery();

        ResultSetMetaData md = resultSet.getMetaData();
        int columns = md.getColumnCount();

        while (resultSet.next()) {
            HashMap<String, Object> row = new HashMap<>(columns);
            for (int i = 1; i <= columns; ++i) {
                row.put(md.getColumnName(i), resultSet.getObject(i));
            }
            resultList.add(row);
        }

        resultSet.close();

        return resultList;

    }

    /**
     * 用于执行对数据库存储过程的调用
     *
     * @param connection   当前数据库连接
     * @param sql          待执行的 sql
     * @param inputParams  入参参数数组
     * @param outputParams 出参参数数组， Types.VARCHAR. etc
     * @return 执行结果
     * @throws SQLException sql 异常抛出，上层捕获统一回滚
     */
    public static Map<String, Object> storedProcedure(Connection connection, String sql, List<Object> inputParams, List<Integer> outputParams) throws SQLException {

        CallableStatement cs = connection.prepareCall(sql);

        // 拼装入参
        if (inputParams != null && inputParams.size() > 0) {
            for (int i = 0; i < inputParams.size(); i++) {
                Object param = inputParams.get(i);
                if (param instanceof String) {
                    cs.setString(i + 1, String.valueOf(param));
                } else if (param instanceof BigDecimal) {
                    cs.setBigDecimal(i + 1, (BigDecimal) param);
                } else {
                    cs.setString(i + 1, String.valueOf(param));
                }
            }
        }

        // 拼装出参
        if (outputParams != null && outputParams.size() > 0) {
            int inputParamsSize = inputParams == null ? 0 : inputParams.size();
            for (int i = 0; i < outputParams.size(); i++) {
                cs.registerOutParameter(inputParamsSize + i + 1, outputParams.get(i));
            }
        }

        cs.executeUpdate();

        Map<String, Object> returnMap = new HashMap<>();

        // 封装执行结果
        if (outputParams != null && outputParams.size() > 0) {
            int inputParamsSize = inputParams == null ? 0 : inputParams.size();
            for (int i = 0; i < outputParams.size(); i++) {
                int currIndex = inputParamsSize + i + 1;
                if (Types.VARCHAR == outputParams.get(i)) {
                    returnMap.put(currIndex + "", cs.getString(currIndex));
                } else if (Types.NUMERIC == outputParams.get(i)) {
                    returnMap.put(currIndex + "", cs.getBigDecimal(currIndex));
                } else {
                    returnMap.put(currIndex + "", cs.getString(currIndex));
                }

            }
        }

        cs.close();

        return returnMap;
    }

    /**
     * 关闭当前连接
     *
     * @param connection 当前连接
     */
    public static void closeConnection(Connection connection) {
        try {
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            logger.error("connection close error: {}", e.getMessage());
            e.printStackTrace();
        }
    }

    /**
     * 回滚操作
     *
     * @param connection 当前连接
     */
    public static void rollback(Connection connection) {
        try {
            if (connection != null) {
                connection.rollback();
            }
        } catch (SQLException e1) {
            logger.info("rollback error: {}", e1.getMessage());
            e1.printStackTrace();
        }
    }

}
